﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.ObjectModel;
using MarginTrading.Entity;
using MarginTrading.Common;
using System.Data.OracleClient;
using System.Data;

namespace MarginTrading.Services
{
    public class InitialSettlementServices
    {
        private static InitialSettlement GetInitialSettlementFromDataReader(IDataReader dataReader)
        {
            InitialSettlement retVal = null;
            if (dataReader != null && ! dataReader.IsClosed)
            {
                retVal = new InitialSettlement();

                retVal.ClientCode = dataReader["CLIENT_CODE"].ToString();

                DateTime AddedDate = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["ADDED_DATE"].ToString(), out AddedDate))
                {
                    retVal.AddedDate = AddedDate;
                }

                DateTime UpdatedDate = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["UPDATED_DATE"].ToString(), out UpdatedDate))
                {
                    retVal.UpdatedDate = UpdatedDate;
                }

                double amount = 0;
                if (double.TryParse(dataReader["AMOUNT"].ToString(), out amount))
                {
                    retVal.Amount = amount;
                }

                amount = 0;
                if (double.TryParse(dataReader["SETTLED_AMOUNT"].ToString(), out amount))
                {
                    retVal.SettledAmount = amount;
                }

                retVal.ReferenceCode = dataReader["REFERENCE"].ToString();

                int iVal = 0;
                if (int.TryParse(dataReader["ACC_POSTED"].ToString(), out iVal))
                {
                    retVal.AccPosted = iVal;
                }

                iVal = 0;
                if (int.TryParse(dataReader["SENT_TO_BROKER"].ToString(), out iVal))
                {
                    retVal.SentToBroker = iVal;
                }
            }
            return retVal;
        }

        public static InitialSettlement GetInitialSettlementByClientCode(string sClientCode)
        {
            InitialSettlement retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM INITIAL_SETTLEMENT WHERE CLIENT_CODE = '" + sClientCode + "'";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = GetInitialSettlementFromDataReader(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static InitialSettlementCollection GetInitialSettlementCollection()
        {
            InitialSettlementCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM INITIAL_SETTLEMENT";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new InitialSettlementCollection();
                    while (dataReader.Read())
                    {
                        InitialSettlement item = GetInitialSettlementFromDataReader(dataReader);
                        if (item != null)
                        {
                            retVal.Add(item);
                        }
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        //public static DataTable GetAgentCollectionDataTable()
        //{
        //    DataTable retVal = null;
        //    OracleConnection con = new OracleConnection();

        //    try
        //    {
        //        con.ConnectionString = Constants.DB_CONNECTION_STRING;
        //        con.Open();
        //        OracleCommand cmd = new OracleCommand();
        //        cmd.CommandText = "select * from agent";
        //        cmd.Connection = con;

        //        using (IDataReader dataReader = cmd.ExecuteReader())
        //        {
        //            if (dataReader != null)
        //            {
        //                retVal = new DataTable();
        //                retVal.Load(dataReader);
        //            }
        //        }
        //    }
        //    catch (System.Exception exc)
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }
        //    return retVal;
        //}

        public static void Add(InitialSettlement settlement)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();

                cmd.CommandText = "INSERT INTO INITIAL_SETTLEMENT(CLIENT_CODE, ADDED_DATE, UPDATED_DATE, AMOUNT, " + 
                "SETTLED_AMOUNT, REFERENCE, ACC_POSTED, SENT_TO_BROKER) VALUES(:v1, :v2, :v3, :v4, :v5, :v6, :v7, :v8)";

                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = settlement.ClientCode;
                cmd.Parameters.Add(":v2", OracleType.DateTime).Value = settlement.AddedDate;
                cmd.Parameters.Add(":v3", OracleType.DateTime).Value = settlement.UpdatedDate;
                cmd.Parameters.Add(":v4", OracleType.Number).Value = settlement.Amount;
                cmd.Parameters.Add(":v5", OracleType.Number).Value = settlement.SettledAmount;
                cmd.Parameters.Add(":v6", OracleType.VarChar).Value = settlement.ReferenceCode;
                cmd.Parameters.Add(":v7", OracleType.Number).Value = settlement.AccPosted;
                cmd.Parameters.Add(":v8", OracleType.Number).Value = settlement.SentToBroker;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        //public static void Delete(string code)
        //{
        //    OracleConnection con = new OracleConnection();
        //    try
        //    {
        //        con.ConnectionString = Constants.DB_CONNECTION_STRING;
        //        con.Open();
        //        OracleCommand cmd = new OracleCommand();
        //        cmd.CommandText = "delete from agent where code = '" + code + "'";
        //        cmd.Connection = con;

        //        cmd.ExecuteNonQuery();
        //    }
        //    catch (System.Exception exc)
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }
        //}

        public static void Update(InitialSettlement settlement)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();

                cmd.CommandText = "UPDATE INITIAL_SETTLEMENT SET " +
                "ADDED_DATE = :v2, " +
                "UPDATED_DATE = :v3, " +
                "AMOUNT = :v4, " +
                "SETTLED_AMOUNT = :v5, " +
                "REFERENCE = :v6, " +
                "ACC_POSTED = :v7," +
                "SENT_TO_BROKER = :v8 " +
                "WHERE CLIENT_CODE = :v1";

                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = settlement.ClientCode;
                cmd.Parameters.Add(":v2", OracleType.DateTime).Value = settlement.AddedDate;
                cmd.Parameters.Add(":v3", OracleType.DateTime).Value = settlement.UpdatedDate;
                cmd.Parameters.Add(":v4", OracleType.Number).Value = settlement.Amount;
                cmd.Parameters.Add(":v5", OracleType.Number).Value = settlement.SettledAmount;
                cmd.Parameters.Add(":v6", OracleType.VarChar).Value = settlement.ReferenceCode;
                cmd.Parameters.Add(":v7", OracleType.Number).Value = settlement.AccPosted;
                cmd.Parameters.Add(":v8", OracleType.Number).Value = settlement.SentToBroker;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static DataTable GetInitialSettlementDataTable(string sBrokerCode, string sAgentCode)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();

                Config config = ConfigServices.GetConfigByName(Constants.CONFIG_NAME_DEPLOYMENT_SIDE);
                if (config.Value == Constants.CONFIG_VALUE_DEPLOYMENT_SIDE_AGENT)
                {
                    cmd.CommandText = "SELECT ISE.CLIENT_CODE, C.SURNAME, C.INITIALS, ISE.AMOUNT FROM INITIAL_SETTLEMENT ISE, CLIENT C WHERE C.CODE = ISE.CLIENT_CODE AND C.BROKER = '"
                        + sBrokerCode + "' AND ISE.ACC_POSTED = 0";
                }
                else
                {
                    cmd.CommandText = "SELECT ISE.CLIENT_CODE, C.SURNAME, C.INITIALS, ISE.AMOUNT FROM INITIAL_SETTLEMENT ISE, CLIENT C WHERE C.CODE = ISE.CLIENT_CODE AND C.AGENT = '"
                        + sAgentCode + "' AND ISE.ACC_POSTED = 0";
                }

                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static InitialSettlementCollection GetToBeSentInitialSettlementCollection(string sBrokerCode, string sAgentCode)
        {
            InitialSettlementCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT INS.* FROM INITIAL_SETTLEMENT INS, CLIENT CL WHERE INS.ACC_POSTED = 1 AND " +
                    "INS.SENT_TO_BROKER = 0 AND CL.BROKER = '" + sBrokerCode + "' AND CL.AGENT = '" + sAgentCode +
                    "' AND CL.CODE = INS.CLIENT_CODE";

                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new InitialSettlementCollection();
                    while (dataReader.Read())
                    {
                        InitialSettlement item = GetInitialSettlementFromDataReader(dataReader);
                        if (item != null)
                        {
                            retVal.Add(item);
                        }
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static void MarkSentInitialSettlementCollection(string sBrokerCode, string sAgentCode)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "UPDATE INITIAL_SETTLEMENT INS SET INS.SENT_TO_BROKER = 1 " + 
                    "WHERE EXISTS (SELECT CL.CODE FROM CLIENT CL WHERE INS.ACC_POSTED = 1 AND " +
                    "INS.SENT_TO_BROKER = 0 AND CL.BROKER = '" + sBrokerCode + "' AND CL.AGENT = '" 
                    + sAgentCode + "'  AND CL.CODE = INS.CLIENT_CODE)";

                cmd.Connection = con;
                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }
    }
}
